﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Models;

namespace DAL
{
    public class OrderDAL
    {
        //改变评论状态
        public static bool updateConmmentDAL(int OrderID)
        {
            string sql=string.Format("update tb_Order set Comments =0 where OrderID={0}",OrderID);
            return DBHelper.ExecuteNonQuery(sql);
        }
        //删除
        public static bool deleteByIDDAL(int OrderID)
        {
            string sql=string.Format("delete from tb_Order where OrderID={0}",OrderID);
            bool res=DBHelper.ExecuteNonQuery(sql);
            return res;
        }
        //增
        public static bool addOederDAL(Order order)
        {
            string sql = string.Format("insert into tb_Order(UserID,OrderMoney,Address,Num,GoodsID,AddressName,AddressPhone)values({0},{1},'{2}',{3},{4},'{5}','{6}')", order.UserID, order.OrderMoney, order.Address,order.Num, order.GoodsID,order.AddressName,order.AddressPhone);
            return DBHelper.ExecuteNonQuery(sql);
        }
        //根据UserID进行查询
        public static List<Order> getAllByUserDAL(int UserID)
        {
            string sql = string.Format("select * from tb_Order where UserID={0}", UserID);
            List<Order> orderList = new List<Order>();
            SqlDataReader dr=DBHelper.GetDataReader(sql);
            while(dr.Read())
            {
                Order order = new Order();
                order.OrderID=dr.GetInt32(0);
                order.UserID=dr.GetInt32(1);
                order.OrderTime=dr.GetDateTime(2);
                order.OrderMoney=dr.GetDecimal(3);
                order.Address=dr.GetString(4);
                order.Num=dr.GetInt32(5);
                order.GoodsID=dr.GetInt32(6);
                order.Comments = dr.GetInt32(7);
                order.State=dr.GetInt32(8);
                order.AddressName=dr.GetString(9);
                order.AddressPhone=dr.GetString(10);
                orderList.Add(order);
            }
            dr.Close();
            return orderList;
        }
        //联表查询Order_Goods
        public static List<Order_Goods> GetOrder_GoodsDAL(int UserID)
        {
            string sql = string.Format("select o.OrderID,o.OrderTime,o.OrderMoney,o.Address,o.Num,o.GoodsID,o.Comments,o.State,g.GoodsPic,g.GoodsName,o.AddressName,o.AddressPhone from tb_Order o,tb_Goods g where o.GoodsID=g.GoodsID and o.UserID={0} order by o.OrderTime desc", UserID);
            List<Order_Goods> list=new List<Order_Goods>();
            SqlDataReader dr=DBHelper.GetDataReader(sql);
            while (dr.Read())
            {
                Order_Goods order_goods = new Order_Goods();
                order_goods.OrderID=dr.GetInt32(0);
                order_goods.OrderTime=dr.GetDateTime(1).ToString("yyyy-MM-dd");
                order_goods.OrderMoney=dr.GetDecimal(2);
                order_goods.Address=dr.GetString(3);
                order_goods.Num=dr.GetInt32(4);
                order_goods.GoodsID=dr.GetInt32(5);
                order_goods.Comments=dr.GetInt32(6);
                order_goods.State=dr.GetInt32(7);
                order_goods.GoodsPic = dr.GetString(8);
                order_goods.GoodsName=dr.GetString(9);
                order_goods.AddressName=dr.GetString(10);
                order_goods.AddressPhone=dr.GetString(11);
                list.Add(order_goods);
            }
            dr.Close ();
            return list;
        }
    }
}
